package com.shh.poi;

import com.shh.pojo.User;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class PoiTest {
    @Test
    public void test1() throws IOException {
        final Workbook workbook = new XSSFWorkbook();
        final Sheet sheet = workbook.createSheet();
        final Row row = sheet.createRow(0);
        row.setHeightInPoints(42.23f);
        final Cell cell = row.createCell(0);
        cell.setCellValue("这是我的第一张");
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setColor(HSSFColor.ROYAL_BLUE.index);
        font.setFontName("黑体");
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setRotation((short) 2);
        try (final FileOutputStream
                     fileOutputStream = new FileOutputStream("src/main/resources/style.xlsx")){
            workbook.write(fileOutputStream);
        } catch (RuntimeException ex) {
            ex.printStackTrace();
        }
    }


    /**
     * 制作模板，加载模板，填充数据并响应给客户端(生成文件)
     * @throws IOException
     */
    @Test
    public void test2() throws IOException {
        try (final InputStream fileInputStream
                     = new FileInputStream("src/main/resources/template.xls");
             final FileOutputStream fileOutputStream
                     = new FileOutputStream("src/main/resources/template_filled_data.xls");){
            Workbook workbook = new HSSFWorkbook(fileInputStream);
            final Sheet sheet = workbook.getSheetAt(0);
            int rowIndex = 2;

            List<User> list = userList();
            for (User user : list) {
                final Row row = sheet.createRow(rowIndex++);

                int cellIndex = 0;

                final Cell cell1 = row.createCell(cellIndex++);
                cell1.setCellType(Cell.CELL_TYPE_STRING);
                cell1.setCellValue(user.getName());

                final Cell cell2 = row.createCell(cellIndex++);
                cell2.setCellType(Cell.CELL_TYPE_STRING);
                cell2.setCellValue(user.getGender());

                final Cell cell3 = row.createCell(cellIndex++);
                cell3.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell3.setCellValue(user.getAge());

                final Cell cell4 = row.createCell(cellIndex++);
                cell4.setCellType(Cell.CELL_TYPE_STRING);
                cell4.setCellValue(user.getTelephone());

                final Cell cell5 = row.createCell(cellIndex++);
                cell5.setCellType(Cell.CELL_TYPE_STRING);
                cell5.setCellValue(user.getEmail());
            }

            workbook.write(fileOutputStream);



        } catch (RuntimeException ex) {
            ex.printStackTrace();
        }

    }


    public List<User> userList(){
        List<User> li = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            User u = new User();
            u.setName("张三");
            u.setAge((i+1)*5);
            u.setTelephone("18888888888");
            u.setGender("男");
            u.setEmail("1512@t96.com");
            li.add(u);
        }
        return li;

    }
}
